import decimal
from datetime import datetime
import pymysql
from click import DateTime
#from clickhouse_driver import connect
from clickhouse_sqlalchemy import make_session
from sqlalchemy import create_engine
import csv
from setting import mysql_config1, mysql_config2, clickhouse_config
import pandas as pd

file_path = r"C:\Users\DELL\Desktop\融资情报局"

# MySQL数据库连接获得产品订单消息
mysql_conn1 = pymysql.connect(**mysql_config1)
mysql_cursor1 = mysql_conn1.cursor()
mysql_query = "SELECT o.company_name as 'company_name', o.product_id 'product_id',p.depart 'depart',p.name as 'product_name'," \
              "p.rate_down as 'rate_down', p.rate_upper as 'rate_upper',p.loan_cycle as 'loan_cycle',p.refund_way as 'refund_way'," \
              "p.money_num as 'money_num',o.credit_line as 'credit_line',o.money as 'money',o.busi_time as 'busi_time' " \
              "FROM chsell_order o INNER JOIN chsell_product p ON o.product_id = p.id " \
              "WHERE o.busi_time >= '2023-09-01 00:00:00' AND LENGTH(o.company_name) > 10 ;"

mysql_cursor1.execute(mysql_query)
data1 = mysql_cursor1.fetchall()
mysql_cursor1.close()
mysql_conn1.close()
#
# # 把数据输出到另外一个45本地数据库中,手动导入到clickhouse中
# mysql_conn2 = pymysql.connect(**mysql_config2)
# mysql_cursor2 = mysql_conn2.cursor()
# insert_sql = "INSERT INTO chsell_order_qingbaoju VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
# mysql_cursor2.executemany(insert_sql, data1)
# mysql_conn2.commit()
# mysql_cursor2.close()
# mysql_conn2.close()




# ClickHouse数据库获得syx公司的工商信息
clickhouse_conn = 'clickhouse://{user}:{password}@{server_host}:{port}/{db}'.format(**clickhouse_config)
engine = create_engine(clickhouse_conn, echo=True)
session = make_session(engine)

#将data1的数据写入clickhouse的default.chsell_order_qingbaoju表中
for i in data1:
    # 如果某个字段的值是'None'则转换为字符串''

    i = tuple(float(x) if isinstance(x,float) or isinstance(x,decimal.Decimal) else x for x in i)
    i = tuple(str(x) if x is not None else '0.0' for x in i)
    # i = tuple(str(x) for x in i)  # 如果是浮点数则转换为字符串
    print(i)

    sql_str = "INSERT INTO default.chsell_order_qingbaoju VALUES('%s')"% "','".join(i)
    print(sql_str)
    res = session.execute("INSERT INTO default.chsell_order_qingbaoju VALUES('%s');"% "','".join(i))
    # res = session.execute("INSERT INTO default.chsell_order_qingbaoju VALUES('嘉兴建昶贸易有限公司', 'abea3f47b6d147f9b0c622dea0425c95', '新网银行', '新网银行-好企e贷', '10.8', '18.0', '12/24/36期', '等额本息,随借随还,先息后本', '3000000', '0.00', '0', '2023-10-01 00:00:00')", i)
    session.commit()
print("数据写入成功！")
session.close()
#将df1的数据写入clickhouse的default.chsell_order_qingbaoju表中
# df1 = pd.DataFrame(data1)
# df1.to_sql('chsell_order_qingbaoju', engine, index=False, if_exists='append')



# clickhouse_query = "SELECT eid, tax_code, company_name, company_category, idx, open_status, open_status_tag, legal_person, " \
#                    "reg_cap_str, reg_cap_num, province, city, town, tel, more_tel, email, more_email, social_credit_code," \
#                    " reg_code, org_code, join_insure_num, company_type, industry, industry_code, pass_name," \
#                    " web_site, addr, new_addr, scope, foreign_trade, reg_date, check_date, gd_lng, gd_lat, province_code, " \
#                    "city_code, town_code, industry_main_code, company_type_code, has_mobile, has_line_phone, has_email, " \
#                    "create_time, update_time, white_list, annual_report_list, total_asset, total_liability, total_sales, " \
#                    "main_business_income, total_profit, net_profit, total_tax, total_equity, is_agent_tel, loan_willing_level, " \
#                    "loan_quota_level, loan_pass_level, is_public_enterprise, is_high_tech_enterprise, is_top500_enterprise, " \
#                    "taxer_qualification, tax_credit_level_a, court_announcement_num, court_session_num, be_executed_num, " \
#                    "high_consum_limit_num, judgment_doc_num, setup_case_num, copyright_num, patent_num, penalty_num, " \
#                    "dishonest_executed_num, legal_rep_hold_ratio, legal_rep_change_date, rec_cap_str, rec_cap_num " \
#                    "FROM tuoke_square_company_info ;"

# # clickhouse_query = 'SHOW TABLES'

# clickhouse_query = "CREATE TABLE default.chsell_order_qingbaoju (`company_name` VARCHAR(255) COMMENT '申请企业名'," \
#                    "`product_id` VARCHAR(32) COMMENT '产品id',`depart` VARCHAR(127) DEFAULT NULL COMMENT '机构'," \
#                    "`name` VARCHAR(60) COMMENT '名称',`rate_down` Float64 COMMENT '产品利率下限',`rate_upper` Float64 COMMENT '产品利率上限'," \
#                    "`loan_cycle` VARCHAR(32) DEFAULT NULL COMMENT '贷款周期',`refund_way` VARCHAR(32) DEFAULT NULL COMMENT '还款方式', " \
#                    "`money_num` Int32 COMMENT '额度(数值,前端排序时用)',`credit_line` Decimal(16, 2) COMMENT '授信額度'," \
#                    "`money` Int64 COMMENT '下款金额(分)',`busi_time` DateTime COMMENT '业务时间(上游真实进件时间)') ENGINE = MergeTree ORDER BY company_name;"
#
# clickhouse_cursor = session.execute(clickhouse_query)



# fields = clickhouse_cursor._metadata.keys
# df2 = pd.DataFrame([dict(zip(fields, item)) for item in clickhouse_cursor.fetchall()])
# print(df2)


# #以df1的company_name为基准，把df2的所有字段数据按照df1的company_name进行合并
# merged_data = pd.merge(df1, df2, how='left', on='company_name')
# print(merged_data)

# # 合并数据
# def merge_data(data1, data2):
#     merged_data = []
#     for row1 in data1:
#         for row2 in data2:
#             if row1[0] == row2[0]:
#                 merged_row = list(row1) + list(row2[1:])
#                 merged_data.append(merged_row)
#                 break
#     return merged_data
#
# # 合并两个数据源的数据
# merged_data = merge_data(data1, data2)
#
#
